from openpyxl import load_workbook
from operator import is_not, attrgetter, itemgetter
from functools import partial
from itertools import takewhile, groupby, islice
from collections import namedtuple, OrderedDict
import re


def parse_excel(excel_file):
    wb = load_workbook(excel_file, data_only=True)
    ws = wb.active

    # 字段名、数据部分
    fields = list(takewhile(partial(is_not, None), next(ws.values)))
    Data = namedtuple('Data', list(map(partial(re.sub, r'[()、]', ''), fields)))
    data = [Data._make(row[:len(fields)]) for row in islice(ws.values, 1, None)]
    data = list(filter(lambda x: x.是否取得发票 == '是', data))
    data.sort(key=lambda x: (x.销方名称, x.发票类型, str(x.发票代码), str(x.发票号码)))

    # 公司分组
    for company, v0 in groupby(data, attrgetter('销方名称')):
        # 以发票类型为键，各项为值
        details = OrderedDict()

        # 发票类型分组
        for tax_category, v1 in groupby(v0, attrgetter('发票类型')):
            details.setdefault(tax_category, {})['invoice_count'] = 0                # 发票数量
            details.setdefault(tax_category, {})['invoice_value'] = 0.0              # 发票金额
            details.setdefault(tax_category, {})['tax_amount'] = 0.0                 # 发票税额
            details.setdefault(tax_category, {})['numbers_group'] = OrderedDict()    # 发票号码归组

            # 发票代码分组
            for invoice_code, v2 in groupby(v1, attrgetter('发票代码')):

                # 发票号码分组（如有连续号码，分为一组）
                for _, v3 in groupby(enumerate(v2), lambda x: int(x[1].发票号码) - int(x[0])):
                    succ = list(map(itemgetter(1), v3))
                    details[tax_category]['invoice_count'] += len(succ)
                    details[tax_category]['invoice_value'] += sum(map(attrgetter('金额元'), succ))
                    details[tax_category]['tax_amount'] += sum(map(attrgetter('税额元'), succ))
                    details[tax_category]['numbers_group'].setdefault(invoice_code, []).append(
                        f'{succ[0].发票号码}至{succ[-1].发票号码}' if len(succ) > 1 else f'{succ[0].发票号码}')

        for category, detail in details.items():
            print(f'取得{company}{category}{detail["invoice_count"]}份，', end='')
            for invoice_code, numbers_group in detail['numbers_group'].items():
                print(f'发票代码：{invoice_code}，发票号码：{"、".join(numbers_group)}，', end='')
            print(f'金额共计{detail["invoice_value"]:,.2f}元，'
                  f'税额合计{detail["tax_amount"]:,.2f}元，'
                  f'价税合计{detail["invoice_value"]+detail["tax_amount"]:,.2f}元。')


parse_excel(r'./data.xlsx')
